﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Data.OleDb;
namespace AutoCompleteText
{
    class OLEDBControl
    {
      //  string strConn;             //DB서버 연결 문자열]
       // SqlConnection conn;
       // SqlCommand comm;
       // SqlDataAdapter adapter;     // Connection과 DataSet의 매개체 클래스

        OleDbConnection conn;
        OleDbCommand comm;
        private const string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;data source=ISUM.accdb;MODE=ReadWrite";
        

        public OLEDBControl()
        {
            try
            {
                this.conn = new OleDbConnection(strConn);
            }catch(Exception)
            {

            }
            //this.strConn = "server=210.118.69.170;database=AutoComplete;user id=sukbaek; password=tjrqor";
            //this.conn = new SqlConnection(this.strConn);

        }

        public void DBOpen()
        {
            try
            {
                if (this.conn.State == ConnectionState.Closed)
                {
                    this.conn.Open();
                }

            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message.ToString());
                Console.WriteLine("DBOpen");
            }
        }

        public void DBClose()
        {
            try
            {
                if (this.conn.State == ConnectionState.Open)
                {
                    this.conn.Close();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message.ToString());
                Console.WriteLine("DBClose");
            }

        }

        public void DBInsert(String contents)
        {
            DBOpen();
            try
            {
                String query = "insert into AutoComplete values (@CONTENTS, @COUNT)";
                OleDbCommand comm = new OleDbCommand(query, this.conn);

                comm.Parameters.Add(new OleDbParameter("@CONTENTS", OleDbType.VarChar)).Value = contents;
                comm.Parameters.Add(new OleDbParameter("@COUNT", OleDbType.Integer)).Value = 1;
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message.ToString());
                Console.WriteLine("DBInsert");
            }
            finally
            {
                DBClose();
            }

        }

        public void DBDelete(String contents)
        {
            DBOpen();
            try
            {
                String query = "delete from AutoComplete where Contents = @CONTENTS";
                OleDbCommand comm = new OleDbCommand(query, this.conn);

                comm.Parameters.Add(new OleDbParameter("@CONTENTS", OleDbType.VarChar)).Value = contents;
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message.ToString());
                Console.WriteLine("DBDelete");
            }
            finally
            {
                DBClose();
            }
        }
        public int DBSelectCnt(String contents)
        {
            int tmpCnt = 0;
            DBOpen();
            try
            {
                String query = "select Count from AutoComplete where Contents = '"+ contents + "'";
                

                 comm = new OleDbCommand(query, this.conn);
                OleDbDataReader sr = comm.ExecuteReader();


                while (sr.Read())
                {

                    tmpCnt = sr.GetInt32(0);
                    Console.WriteLine(sr.GetValue(0).ToString());
                    //Console.WriteLine(sr.GetValue(1).ToString());
                }
                sr.Close();

            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message.ToString());
                Console.WriteLine("DBSelectCnt");
            }
            finally
            {
                DBClose();
            }
            return tmpCnt;
        }

        public void DBUpdate(String contents, String chgContents, int chgCount)
        {
            DBOpen();
            try
            {
                String query = "update AutoComplete set Contents = @chgCONTENTS, Count = @chgCOUNT where Contents = @CONTENTS";
                OleDbCommand comm = new OleDbCommand(query, this.conn);

                comm.Parameters.Add(new OleDbParameter("@CONTENTS", OleDbType.VarChar)).Value = contents;
                comm.Parameters.Add(new OleDbParameter("@chgCONTENTS", OleDbType.VarChar)).Value = chgContents;
                comm.Parameters.Add(new OleDbParameter("@chgCOUNT", OleDbType.Integer)).Value = chgCount;
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message.ToString());
                Console.WriteLine("DBUpdate");
            }
            finally
            {
                DBClose();
            }
        }
        public void DBReset()
        {
            DBOpen();
            try
            {
                String query = "delete from AutoComplete";
                OleDbCommand comm = new OleDbCommand(query, this.conn);

                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message.ToString());
                Console.WriteLine("DBReset");
            }
            finally
            {
                DBClose();
            }
        }

        public List<string> FSSearchKor(String inputStr)
        {
            FSSearch fsSearch = new FSSearch();
            List<string> cities = new List<string>();
            DBOpen();
            String[] queryString = new String[2];
            queryString = fsSearch.Direct(inputStr);

            try
            {
                String query = "select * from AutoComplete where Contents >= '" + queryString[0] + "' and  Contents <'" + queryString[1] + "' order by Count desc, Contents asc";
                comm = new OleDbCommand(query, this.conn);
                OleDbDataReader sr = comm.ExecuteReader();


                while (sr.Read())
                {
                    cities.Add(sr.GetValue(0).ToString());
                    Console.WriteLine(sr.GetValue(0).ToString());
                    Console.WriteLine(sr.GetValue(1).ToString());
                }
                sr.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message.ToString());
                Console.WriteLine("FSSearchKor");
            }
            finally
            {
                DBClose();
            }

            return cities;
        }

        public List<string> FSSearchEng(String inputStr)
        {
            DBOpen();
            List<string> cities = new List<string>();
            try
            {
                String query = "select * from AutoComplete where Contents Like '" + inputStr.Substring(0, 1) + "%' order by Count desc, Contents asc";
                comm = new OleDbCommand(query, this.conn);
                OleDbDataReader sr = comm.ExecuteReader();


                while (sr.Read())
                {
                    cities.Add(sr.GetValue(0).ToString());
                    Console.WriteLine(sr.GetValue(0).ToString());
                    Console.WriteLine(sr.GetValue(1).ToString());
                }
                sr.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message.ToString());
                Console.WriteLine("FSSearchKor");
            }
            finally
            {
                DBClose();
            }
            return cities;
        }


    }
}
